/*
 * @Author: RYPY
 * @Date: 2021-03-21 14:13:39
 * @LastEditors: RYPY
 * @LastEditTime: 2021-03-21 17:09:11
 */
const express=require("express");
const DB=require("../until/DB");
const {Msg,getSQLByObj,serverPath}=require("../until/config");

const router=express();
//地区分类显示
router.get("/areas",async(req,res,next)=>{
    let sql=`select * from e_area`;
    let [err,result]=await DB.query(sql);
    if(!err){
        res.send(Msg(200,"ok",result));
    }else{
        next("请稍后再试！");
    }
});
//年级分类显示
router.get("/grades",async(req,res,next)=>{
    let sql=`select * from e_grade`;
    let [err,result]=await DB.query(sql);
    if(!err){
        res.send(Msg(200,"ok",result));
    }else{
        next("请稍后再试");
    }
});
//科目分类显示
router.get("/subjects",async(req,res,next)=>{
    let sql=`select * from e_subject`;
    let [err,result]=await DB.query(sql);
    if(!err){
        res.send(Msg(200,"ok",result));
    }else{
        next("请稍后再试");
    }
});
//考试分类显示
router.get("/cates",async(req,res,next)=>{
   let sql=`select * from e_category`;
   let [err,result]=await DB.query(sql);
   if(!err){
       res.send(Msg(200,"ok",result));
   } else{
       next("请稍后再试");
   }
});

//根据不同的分类查询课程
router.post("/courses",async(req,res,next)=>{
   let {type=1,pageNumber=1,pageSize=6,orderby="id",sort="desc",grand="",subject="",category="",area=""}=req.body;
   if(pageNumber===0||!isNaN(pageNumber)){
       pageNumber=1;
   }
   let start=(pageNumber-1)*pageSize;
   let sqlstr= getSQLByObj({grand,subject,category,area});
   let sql1=`select c.id,c.cid,title,price,concat('${serverPath}/course_img/',image_src) image_src,type,type_name,area_name,grade_name,subject_name,category_name,tid,c.status,count(o.cid) num  from e_course c left join e_order o on c.cid=o.cid where type=${type} ${sqlstr?`and ${sqlstr}`:" "} group by c.cid order by ${orderby} ${sort} limit ${start},${pageSize}`;
   let [err1,result1]=await DB.query(sql1);
   //数据的数量统计
   let sql2=`select count(*) total from e_course where type=${type} ${sqlstr?`and ${sqlstr}`:""}`;
   let [err2,result2]=await DB.query(sql2);
   let total=result2[0].total;
   let pages=Math.ceil(total/pageSize);
   let data={
       pageSize,
       pageNumber,
       totalRow:total,
       totalPage:pages,
       result:result1
   };
   if(!err1||!err2){
       res.send(data);
   }  else{
       next("稍后再试");
   }
});

//根据关键词搜索课程接口
router.get("/search_list",async(req,res,next)=>{
    let {page=1,limit=6,orderby ="id",sort="desc",keyword=""}=req.query;
    let start=(page-1)*limit;
    if(!keyword){
        next("请输入正确的关键词");
    }
    let sql1=`select c.id,c.cid,title,price,concat('${serverPath}/course_img/',image_src),type,type_name,area_name,grade_name,subject_name,category_name,c.status,tid,count(o.cid) num from e_course c left join e_order o on c.cid=o.cid where title like '%${keyword}%' group by c.cid order by ${orderby} ${sort} limit ${start},${limit}`;
    let [err1,result1]=await DB.query(sql1);
    let sql2=`select count(*)  total from e_course where title like '%${keyword}%'`;
    let [err2,result2]=await DB.query(sql2);
    let total=result2[0].total;
    let pages=Math.ceil(total/limit);
    let data={
        total,
        
        pages,
        result:result1
    }
    if(!err1||!err2){
        res.send(Msg(200,"ok",data));
    }else{
        next("请稍后再试");
    }
});


module.exports=router;